from typing import Optional
from urllib.parse import urlparse
import threading
import pymysql
from intelli_port.config import load_settings
from intelli_port.commons.logging import json_logger


class MySQLPool:
    def __init__(self, uri: str, min_size: int, max_size: int, connect_timeout: int):
        self.uri = uri
        self.min_size = min_size
        self.max_size = max_size
        self.connect_timeout = connect_timeout
        self._lock = threading.RLock()
        self._pool: list[pymysql.connections.Connection] = []
        for _ in range(max(0, min_size)):
            c = self._create()
            if c:
                self._pool.append(c)

    def _create(self) -> Optional[pymysql.connections.Connection]:
        if not self.uri:
            return None
        u = urlparse(self.uri)
        host = u.hostname or ""
        port = u.port or 3306
        user = u.username or ""
        password = u.password or ""
        db = (u.path or "/").lstrip("/") or None
        conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=db,
            connect_timeout=self.connect_timeout,
            charset="utf8mb4",
            use_unicode=True,
        )
        try:
            cur = conn.cursor()
            try:
                cur.execute("SET NAMES utf8mb4")
                cur.execute("SET collation_connection = utf8mb4_general_ci")
            finally:
                cur.close()
        except Exception:
            pass
        # Optional: attempt database-level fix when enabled
        try:
            s = load_settings()
            if getattr(s, "mysql_auto_fix_charset", False) and db:
                cur = conn.cursor()
                try:
                    cur.execute("SHOW VARIABLES LIKE 'character_set_database'")
                    r = cur.fetchone()
                    charset_db = (r[1] if r and len(r) > 1 else "")
                    if str(charset_db).lower() != "utf8mb4":
                        try:
                            cur.execute(f"ALTER DATABASE `{db}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci")
                            json_logger().info("db.charset_fix", extra={"service": "api", "database": db, "from": charset_db, "to": "utf8mb4"})
                        except Exception:
                            json_logger().info("db.charset_fix_failed", extra={"service": "api", "database": db, "from": charset_db})
                finally:
                    cur.close()
        except Exception:
            pass
        return conn

    def acquire(self) -> Optional[pymysql.connections.Connection]:
        with self._lock:
            if self._pool:
                c = self._pool.pop()
            else:
                c = self._create()
            try:
                if c:
                    c.ping(reconnect=True)
                    try:
                        cur = c.cursor()
                        try:
                            cur.execute("SET NAMES utf8mb4")
                            cur.execute("SET collation_connection = utf8mb4_general_ci")
                        finally:
                            cur.close()
                    except Exception:
                        pass
            except Exception:
                try:
                    if c:
                        c.close()
                except Exception:
                    pass
                c = self._create()
            return c

    def release(self, conn: Optional[pymysql.connections.Connection]) -> None:
        if not conn:
            return
        with self._lock:
            try:
                if len(self._pool) < self.max_size:
                    self._pool.append(conn)
                else:
                    conn.close()
            except Exception:
                pass

    def close(self) -> None:
        with self._lock:
            while self._pool:
                c = self._pool.pop()
                try:
                    c.close()
                except Exception:
                    pass


_pool: Optional[MySQLPool] = None
_pool_read: Optional[MySQLPool] = None


def get_mysql() -> Optional[MySQLPool]:
    global _pool
    if _pool is None:
        s = load_settings()
        _pool = MySQLPool(s.mysql_uri, int(getattr(s, "mysql_pool_min_size", 1)), int(getattr(s, "mysql_pool_max_size", 10)), int(getattr(s, "mysql_connect_timeout", 3)))
    return _pool


def get_mysql_read() -> Optional[MySQLPool]:
    global _pool_read
    if _pool_read is None:
        s = load_settings()
        if not s.mysql_read_uri:
            _pool_read = None
        else:
            _pool_read = MySQLPool(s.mysql_read_uri, int(getattr(s, "mysql_pool_min_size", 1)), int(getattr(s, "mysql_pool_max_size", 10)), int(getattr(s, "mysql_connect_timeout", 3)))
    return _pool_read